<?php
 
date_default_timezone_set('America/Los_Angeles');
$gstartdate = date('m/d/Y h:i:s a', time());
$table_name = $argv[1];
$data_type = "";
$TABLE_LIST = "";
$comma = "";
debug("\r\n".$gstartdate.":".$table_name.".....Start");
processSelect($table_name);
 
//---------------------------------------------------------------- 
function debug($v){     
   echo "\r\n"; 
   echo $v."\r\n";     
   echo "\r\n";
   }
function truncateTable($table_name_in,$oracle_conn_in)
{
    debug("\r\n Truncate ".$table_name_in);
    $sql = "TRUNCATE TABLE VFP_".$table_name_in;
  	$stid = oci_parse($oracle_conn_in, $sql);
    $e = oci_execute($stid ); 
	if (!$e ) {
      $m = oci_error($stid);
	  debug('error on truncate');
     }
    oci_free_statement($stid);	
}
function  processSelect($table_name_in )
{
 $commit_count = 0;
 $my_file = "C:\VFPDATA\sql\VFP_".$table_name_in.'.sql';
 //unlink( $my_file);
 $handle = fopen($my_file, 'w') or die('Cannot open file:  '.$my_file); 



 // $db = "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))" ;
 // $oracle_conn = oci_connect("FOXY", "FOXY",  $db);
  $vfp_conn = new COM("ADODB.Connection") or die("Cannot start ADO");
  $vfp_conn->Open("Provider=VFPOLEDB.1;Data Source=C:\\VFPDATA\\DATA\\evrs.dbc;Collating Sequence=machine");
  $sql = "SELECT * FROM " . $table_name_in;
  $rs = $vfp_conn->Execute($sql);  
  $sql_head = createInsertStringHead($rs,$table_name_in);
  //truncateTable($table_name_in,$oracle_conn);
  $rowcount = 0;
  if(!$rs->EOF){
   
    $t = "truncate table VFP_" . $table_name_in;
    fwrite($handle, $t.";\r\n");
	while (!$rs->EOF) {

	 $rowcount ++;

    try{
     $sql_data = createSqlData($rs);
	 }catch(Exception $e)
	 {
	   echo "\r\n================================================================================";
	   echo "\r\n".$e->getMessage()."\r\n";
	   echo "================================================================================";
	 }
	 $sql_insert = $sql_head.$sql_data;
     //debug($sql_insert);
	 fwrite($handle, $sql_insert.";\r\n");
	 if($commit_count > 99)
	 {
	   $commit_count = 0;
	    fwrite($handle, "commit;\r\n");
	 } 
	   $commit_count++;
	 //$stid = oci_parse($oracle_conn, $sql_insert);
	 try{
   //  $e = oci_execute($stid ); 
	 }catch(Exception $e)
	 {
	   echo "\r\n================================================================================";
	   echo "\r\n".$e->getMessage()."\r\n";
	   echo "================================================================================";
	 }
	 
 
     //oci_free_statement($stid);	        
     $rs->MoveNext();
  
	}
	fwrite($handle, "commit;\r\n");
	try{	  
	  debug("\r\n Count:".$rowcount);
      $rs->Close();
	  $rs = null; 
	  $vfp_conn->Close();
	  fclose($handle);
	  //oci_close ( $oracle_conn );
	  //$oracle_conn = null;
	  $vfp_conn = null;
	 }catch(Exception $e)
	 {
	   echo "\r\n================================================================================";
	   echo "\r\n".$e->getMessage()."\r\n";
	   echo "================================================================================";
	 }
  }
}
function createSqlData($rs_in)
{    $sql = "";
     $comma = "";
     $num_columns = $rs_in->Fields->Count();
     for ($i=0; $i < $num_columns; $i++) {
     $fld = $rs_in->Fields($i);
	
	  $sql = $sql.$comma;
	 switch ( $fld->type) {
    case 129:
        //debug("varchar2");
                $my_string = ""; 
		$my_string = trim(preg_replace('/\s\s+/', ' ', $fld->value));
		$my_string = str_replace("'", "", $my_string);
		$my_string = removeAccents( $my_string);
		
		
		$sql = $sql."'".$my_string."'" ;
        break;
    case 5:
        //debug("NUMBER");//FLOAT
		
		$sql = $sql.checkForNumber($fld->value);
        break;
   case 3:
        //debug("NUMBER");//INT
       $sql = $sql.checkForNumber($fld->value);
		break;
   case 131:
        //debug( "NUMBER");//INT
		$sql = $sql.checkForNumber($fld->value);
        break;		
   case 11:
       //debug("VARCHAR2(5)");//BOOLEAN
	    $my_string = trim(preg_replace('/\s\s+/', ' ', $fld->value));
		$my_string = str_replace("'", "", $my_string);
		$my_string = removeAccents($my_string);
		$my_string = trim($my_string);
        if( is_null($my_string) === 1){$my_string = 'F';}
	    if( empty($my_string) === 1){$my_string = 'F';}
	    if( $my_string === '1'){$my_string = 'T';}
	    if( $my_string === '0'){$my_string = 'F';}
	    if(strlen( $my_string ) === 0 ){$my_string = 'F';}
		$sql = $sql."'".$my_string."'" ;
        break;		
		
   case 201:
        //debug( "VARCHAR2");//BOOLEAN
		$my_string = trim(preg_replace('/\s\s+/', ' ', $fld->value));
		$my_string = str_replace("'", "", $my_string);
		$my_string = removeAccents( $my_string);
		$my_string = trim($my_string);
		if( is_null($my_string) === 1){$my_string = 'F';}
		if( empty($my_string) === 1){$my_string = 'F';}
	    if( $my_string === '1'){$my_string = 'T';}
	    if( $my_string === '0'){$my_string = 'F';}
	    if(strlen( $my_string ) === 0 ){$my_string = 'F';}
	 
		$sql = $sql."'".$my_string."'" ;
        break;		
    case 135:
	try{
	   $test_date = $fld->value;
	   if(preg_match("/^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$/", $test_date ) === 0) 
	   {$test_date = "";}
	   if(strtotime( $test_date) === false) 
	   {
	     $sql = $sql."NULL" ;
	   }
	     else
	   {
	     $test_date_mon = explode(' ',$test_date);
		 $sql = $sql."TO_DATE('".$test_date_mon[0]."','MM/DD/YYYY')" ;
	   }
       // debug("DATE");//DATE
		  }catch(Exception $e)
	{
	  echo $e->getMessage();
	  $sql = $sql."NULL" ;
	}
        break;
    case 133:
	try{
        $test_date = $fld->value;
	    if(preg_match("/^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$/", $test_date ) === 0) 
	    {$test_date = "";}
	    if(strtotime(  $test_date) === false) 
	   {
	     $sql = $sql."NULL";
	   }
	     else
	   {
	     
	     $test_date_mon = explode(' ',$test_date);
	     $sql = $sql."TO_DATE('".$test_date_mon[0]."','MM/DD/YYYY')";
	   }
	   		  }catch(Exception $e)
	{
	  echo $e->getMessage();
	  $sql = $sql."NULL" ;
	}
        break;
	default:
	    debug("\r\n=======================================================\r\n");
		debug("NOT FOUND ".$fld[$i]->type);
		debug("\r\n=======================================================\r\n");
}        
	  
 
	 //$sql = 
	 $comma = ",";
   }
   return $sql.')';
}
function checkForNumber($num)
{
  $new_num = 0;
  try{
  //if(is_nan($num)){echo "found it"."\r\n"; return 0;}
  if(is_numeric ($num))
  {
    $new_num = $num;
  }
  }catch(Exception $a)
  {
      $new_num = 0;
  }
  return $new_num;
}
function removeAccents($string) {

    $search = preg_replace('/[àáâãåäæ]/iu', '', $string);
    $search = preg_replace('/[èéêë]/iu', '', $search);
    $search = preg_replace('/[ìíîï]/iu', '', $search);
    $search = preg_replace('/[òóôõöø]/iu', '', $search);
    $search = preg_replace('/[ùúûü]/iu', '', $search);
	$search = preg_replace("/[\r\n]+/", "", $search);
    //$my_string = preg_replace('~&([a-z]{1,2})(?:acute|cedil|circ|grave|lig|orn|ring|slash|th|tilde|uml|caron);~i', '$1', htmlentities($string, ENT_COMPAT, 'UTF-8'));
    $my_string = str_replace("<", "", $search);
	$my_string = str_replace(">", "", $my_string);
	$my_string = str_replace("&", "", $my_string);
 return $my_string; 
}
function createInsertStringHead($rs_in,$table_name_in)
{   
   $comma = "";
   $sql = "INSERT INTO VFP_".strtoupper($table_name_in)."(";
   $num_columns = $rs_in->Fields->Count(); 
   for ($i=0; $i < $num_columns; $i++) {
     $fld[$i] = $rs_in->Fields($i);
	 $fld_name = strtoupper($fld[$i]->name);
	 $sql = $sql.$comma."\"".$fld_name."\"";
	 $comma = ",";
   }
   return $sql.")VALUES(";
}
//-----------------------------------------------------------------
$gstartdate = date('m/d/Y h:i:s a', time());
echo "\r\n";
debug("\r\n".$gstartdate.":".$table_name.".....Done");
?>